package lib.db.clip;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import lib.db.pool.MySQLConnection;

import inet.util.Logger;

public class ClipCategoryDAO {
	private Logger logger = null;
	private MySQLConnection dbPool = null;
	public ClipCategoryDAO(){
		logger =new Logger(this.getClass().getName());
		try {
			dbPool = new MySQLConnection("private_db");
		} catch (Exception e) {
		}
	}
	
	public boolean insertRow(String name, int position) {
		Connection conn = null;
		PreparedStatement psmt = null;
		String strSQL = null;
		boolean result = false;
		try {
			strSQL = " INSERT INTO clip_category( name, position, gen_date, last_updated) " +
					 " VALUES (?, ?, NOW(), NOW()) ";
			conn = dbPool.getConnection();//getConnection();
			psmt = conn.prepareStatement(strSQL);
			psmt.setString(1, name);
			psmt.setInt(2, position);
			
			if(psmt.executeUpdate() > 0){
				result = true;
			}
		
		} catch (Exception e) {
			logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + e.toString());
			System.out.println("insertRow: Error executing SQL " + strSQL + ">>>" + e.toString());
		}finally{
			dbPool.releaseConnection(conn, psmt );
		}
		return result;
	}
	
	public ClipCategory getRow(BigDecimal id) {
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String strSQL = null;
		ClipCategory category = null;
		
		try {
			conn = dbPool.getConnection();
			strSQL = " SELECT id, name, position, gen_date, last_updated" +
					 " FROM clip_category WHERE id = ? ";
			psmt = conn.prepareStatement(strSQL);
			psmt.setBigDecimal(1, id);
			rs = psmt.executeQuery(); 
			if (rs.next()) {
				category = new ClipCategory();
				category.setId(rs.getBigDecimal(1));
				category.setName(rs.getString(2));
				category.setPosition(rs.getInt(3));
				category.setGenDate(rs.getTimestamp(4));
				category.setLastUpdated(rs.getTimestamp(5));
			}
		} catch (Exception e) {
			logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
		} finally{
			dbPool.releaseConnection(conn, psmt, rs);
		}
		return category;
	}
	
	@SuppressWarnings("unchecked")
	public Vector findAll(int currPage, int rowsPerPage) {
		int startRows = (currPage - 1)*rowsPerPage;
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String strSQL = null;
		ClipCategory category = null;
		Vector vCate = null;
		
		try {
			conn = dbPool.getConnection();
			strSQL =" SELECT id, name, position, gen_date, last_updated " +
					" FROM clip_category " +
					" ORDER BY position, gen_date DESC " +
					" LIMIT ? , ? ";
			psmt = conn.prepareStatement(strSQL);
			psmt.setInt(1, startRows);
			psmt.setInt(2, rowsPerPage);
			
			rs = psmt.executeQuery(); 
			vCate = new Vector();
			while (rs.next()) {
				category = new ClipCategory();
				category.setId(rs.getBigDecimal(1));
				category.setName(rs.getString(2));
				category.setPosition(rs.getInt(3));
				category.setGenDate(rs.getTimestamp(4));
				category.setLastUpdated(rs.getTimestamp(5));
				
				vCate.addElement(category);
			}
		} catch (Exception e) {
			logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
			System.out.println("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
		} finally{
			dbPool.releaseConnection(conn, psmt, rs);
		}
		return vCate;
	}
	
	public boolean updateRow(BigDecimal id, String name, int position) {
		Connection conn = null;
		PreparedStatement psmt = null;
		String strSQL = null;
		boolean result = false;
		try {
			strSQL = " UPDATE clip_category SET name = ?, position = ?, last_updated = NOW() " +
			" WHERE id = ? ";
			conn = dbPool.getConnection();//getConnection();
			psmt = conn.prepareStatement(strSQL);
			psmt.setString(1, name);
			psmt.setInt(2, position);
			psmt.setBigDecimal(3, id);
			
			if(psmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception e) {
			logger.error("updateRow: Error executing SQL " + strSQL + ">>>" + e.toString());
		}finally{
			dbPool.releaseConnection(conn, psmt );
		}
		return result;
	}
	
	public boolean deleteRow(BigDecimal id) {
		Connection conn = null;
		PreparedStatement psmt = null;
		String strSQL = null;
		boolean result = false;
		try {
			strSQL = " DELETE FROM clip_category WHERE id = ? ";
			conn = dbPool.getConnection();//getConnection();
			psmt = conn.prepareStatement(strSQL);
			psmt.setBigDecimal(1, id);
			
			if(psmt.executeUpdate() > 0){
				result = true;
			}
			
		} catch (Exception e) {
			logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + e.toString());
		}finally{
			dbPool.releaseConnection(conn, psmt );
		}
		return result;
	}
	
	public boolean hasClipInCate(BigDecimal cateId) {
		Connection conn = null;
		PreparedStatement psmt = null;
		String strSQL = null;
		ResultSet rs = null;
		boolean result = false;
		try {
			strSQL = " SELECT id FROM clip WHERE cate_id = ? ";
			conn = dbPool.getConnection();//getConnection();
			psmt = conn.prepareStatement(strSQL);
			psmt.setBigDecimal(1, cateId);
			rs = psmt.executeQuery();
			if(rs.next()){
				result = true;
			}
			
		} catch (Exception e) {
			logger.error("hasClipInCate: Error executing SQL " + strSQL + ">>>" + e.toString());
		}finally{
			dbPool.releaseConnection(conn, psmt, rs );
		}
		return result;
	}		
}
